Imports System.Data.Common
Imports System.Web.Configuration
Imports System.Reflection
Imports MySql.Data
Imports System.IO

Public Class DataProvider
    Implements IDisposable

    Private objConnection As DbConnection
    Private objCommand As DbCommand
    Private objFactory As DbProviderFactory = Nothing
    Private boolHandleErrors As Boolean
    Private strLastError As String
    Private boolLogError As Boolean
    Private strLogFile As String
    Private _CurrentDatabaseType As DatabaseType
    Private _ConnectionString As String

    Public Enum DatabaseType
        Oracle = 1
        Access = 2
        SQLServer2005 = 3
        SQLServer2000 = 4
        MySQL = 5
        GenericOLEDB = 6
        GenericODBC = 7
    End Enum

    Public ReadOnly Property CurrentDatabaseType()
        Get
            Return _CurrentDatabaseType
        End Get
    End Property

    Public ReadOnly Property ConnectionString()
        Get
            Return _ConnectionString
        End Get
    End Property

    Public Sub New(ByVal DBType As String, ByVal DBServer As String, ByVal DBUser As String, _
    ByVal DBPassword As String, ByVal DBDatabaseName As String, ByVal ConnectString As String)
        Dim connString As String = Nothing
        If String.IsNullOrEmpty(DBType) Then
            Throw New Exception("The database type is either empty or currently unsupported. " & _
            "The current supported types are Access, SQLServer2005, SQLServer2000, Oracle, MySQL, " & _
            "GenericOleDB, and GenericODBC")
        Else
            'Figure out which type of OLEDB Database is being used
            Select Case DBType
                Case "Access"
                    objFactory = OleDb.OleDbFactory.Instance
                    _CurrentDatabaseType = DatabaseType.Access
                    connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
                    If System.Web.VirtualPathUtility.IsAppRelative(DBDatabaseName) Then
                        connString = connString & System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath & DBDatabaseName.Remove(0, 2) & ";"
                    Else
                        connString = connString & DBDatabaseName & ";"
                    End If

                    'Next check the password for the access database
                    If DBPassword = String.Empty Then
                        'Set the username for the access database
                        If DBUser = String.Empty Then
                            connString = connString & "User Id=admin;"
                        Else
                            connString = connString & "User Id=" & DBUser & ";"
                        End If
                        connString = connString & "Password=" & DBPassword & ";"
                    Else
                        connString = connString & "Jet OLEDB:Database Password=" & DBPassword & ";"
                    End If
                Case "SQLServer2005"
                    objFactory = SqlClient.SqlClientFactory.Instance
                    _CurrentDatabaseType = DatabaseType.SQLServer2005
                    connString = "Server=" & DBServer & ";" & _
                    "Database=" & DBDatabaseName & ";" & _
                    "User ID=" & DBUser & ";" & _
                    "Password=" & DBPassword & ";" & _
                    "Trusted_Connection=False;"
                Case "SQLServer2000"
                    objFactory = SqlClient.SqlClientFactory.Instance
                    _CurrentDatabaseType = DatabaseType.SQLServer2000
                    connString = "Provider=SQLOLEDB;Data Source=" & DBServer & _
                    ";Initial Catalog=" & DBDatabaseName & ";UserId=" & _
                    DBUser & ";Password=" & DBPassword & ";"
                Case "Oracle"
                    objFactory = OracleClient.OracleClientFactory.Instance
                    _CurrentDatabaseType = DatabaseType.Oracle
                    connString = "Provider=MSDAORA;Data Source=" & DBDatabaseName & _
                    ";UserId=" & DBUser & ";Password=" & DBPassword & ";"
                Case "MySQL"
                    objFactory = MySqlClient.MySqlClientFactory.Instance
                    _CurrentDatabaseType = DatabaseType.MySQL
                    connString = "Database=" & DBDatabaseName & ";Data Source=" & DBServer & _
                    ";User Id=" & DBUser & ";Password=" & DBPassword & ""
                Case "GenericOleDB"
                    objFactory = OleDb.OleDbFactory.Instance
                    _CurrentDatabaseType = DatabaseType.GenericOLEDB
                    connString = ConnectString
                Case "GenericODBC"
                    objFactory = Odbc.OdbcFactory.Instance
                    _CurrentDatabaseType = DatabaseType.GenericODBC
                    connString = ConnectString
                Case Else
                    Throw New Exception("The database type is either empty or currently unsupported. " & _
                    "The current supported types are Access, SQLServer2005, SQLServer2000, Oracle, MySQL, " & _
                    "GenericOleDB, and GenericODBC")
            End Select
        End If
        _ConnectionString = connString
        objConnection = objFactory.CreateConnection
        objCommand = objFactory.CreateCommand
        objConnection.ConnectionString = connString
        objCommand.Connection = objConnection
    End Sub

    Public ReadOnly Property LastError() As String
        Get
            Return strLastError
        End Get
    End Property

    Public Property LogErrors() As Boolean
        Get
            Return boolLogError
        End Get
        Set(ByVal value As Boolean)
            boolLogError = value
        End Set
    End Property

    Public Property LogFile() As String
        Get
            Return strLogFile
        End Get
        Set(ByVal value As String)
            strLogFile = value
        End Set
    End Property

    Public Function AddParameter(ByVal name As String, ByVal value As Object, ByVal datatype As DbType) As Integer
        Select Case _CurrentDatabaseType
            Case DatabaseType.Access
                Dim p As DbParameter = objFactory.CreateParameter
                p.ParameterName = name
                'Handle specific datatypes for MSAccess since Dates and others need to be converted to strings
                Select Case datatype
                    Case DbType.Date
                        If value Is Nothing Then
                            p.IsNullable = True
                            p.Value = DBNull.Value
                        Else
                            p.Value = value.ToString
                        End If
                    Case DbType.DateTime
                        If (value Is Nothing) Then
                            p.IsNullable = True
                            p.Value = DBNull.Value
                        Else
                            p.Value = value.ToString
                        End If
                    Case Else
                        If (value Is Nothing) Then
                            p.IsNullable = True
                            p.Value = DBNull.Value
                        Else
                            p.Value = value
                        End If
                End Select
                p.DbType = datatype
                Return objCommand.Parameters.Add(p)
            Case DatabaseType.GenericODBC
                Dim p As DbParameter = objFactory.CreateParameter
                p.ParameterName = name
                If (value Is Nothing) Then
                    p.IsNullable = True
                    p.Value = DBNull.Value
                Else
                    p.Value = value
                End If
                p.DbType = datatype
                Return objCommand.Parameters.Add(p)
            Case DatabaseType.GenericOLEDB
                Dim p As DbParameter = objFactory.CreateParameter
                p.ParameterName = name
                If (value Is Nothing) Then
                    p.IsNullable = True
                    p.Value = DBNull.Value
                Else
                    p.Value = value
                End If
                p.DbType = datatype
                Return objCommand.Parameters.Add(p)
            Case DatabaseType.MySQL
                Dim p As DbParameter = objFactory.CreateParameter
                p.ParameterName = name.Replace("@", "?")
                If (value Is Nothing) Then
                    p.IsNullable = True
                    p.Value = DBNull.Value
                Else
                    p.Value = value
                End If
                p.DbType = datatype
                Return objCommand.Parameters.Add(p)
            Case DatabaseType.Oracle
                Dim p As DbParameter = objFactory.CreateParameter
                p.ParameterName = name
                If (value Is Nothing) Then
                    p.IsNullable = True
                    p.Value = DBNull.Value
                Else
                    p.Value = value
                End If
                p.DbType = datatype
                Return objCommand.Parameters.Add(p)
            Case DatabaseType.SQLServer2000
                Dim p As DbParameter = objFactory.CreateParameter
                p.ParameterName = name
                If (value Is Nothing) Then
                    p.IsNullable = True
                    p.Value = DBNull.Value
                Else
                    p.Value = value
                End If
                p.DbType = datatype
                Return objCommand.Parameters.Add(p)
            Case DatabaseType.SQLServer2005
                Dim p As DbParameter = objFactory.CreateParameter
                p.ParameterName = name
                If (value Is Nothing) Then
                    p.IsNullable = True
                    p.Value = DBNull.Value
                Else
                    p.Value = value
                End If
                p.DbType = datatype
                Return objCommand.Parameters.Add(p)
            Case Else
                Throw New Exception("Unknown database types")
        End Select
    End Function

    Public Function AddParameter(ByVal parameter As DbParameter) As Integer
        Return objCommand.Parameters.Add(parameter)
    End Function

    Public ReadOnly Property Command() As DbCommand
        Get
            Return objCommand
        End Get
    End Property

    Public Sub BeginTransaction()
        If objConnection.State = System.Data.ConnectionState.Closed Then
            objConnection.Open()
        End If
        objCommand.Transaction = objConnection.BeginTransaction
    End Sub

    Public Sub CommitTransaction()
        objCommand.Transaction.Commit()
        objConnection.Close()
    End Sub

    Public Sub RollbackTransaction()
        objCommand.Transaction.Rollback()
        objConnection.Close()
    End Sub

    Public Function ExecuteNonQuery(ByVal query As String) As Integer
        Return ExecuteNonQuery(query, CommandType.Text, ConnectionState.CloseOnExit)
    End Function

    Public Function ExecuteNonQuery(ByVal query As String, ByVal commandtype As CommandType) As Integer
        Return ExecuteNonQuery(query, commandtype, ConnectionState.CloseOnExit)
    End Function

    Public Function ExecuteNonQuery(ByVal query As String, ByVal connectionstate As ConnectionState) As Integer
        Return ExecuteNonQuery(query, CommandType.Text, connectionstate)
    End Function

    Public Function TableExists(ByVal TableName As String) As Boolean
        Try
            Dim returnVal As Boolean = False
            If objConnection.State = System.Data.ConnectionState.Closed Then
                objConnection.Open()
            End If
            Dim schemaReader As DataTable = Nothing
            schemaReader = objConnection.GetSchema("Tables")
            For Each rowItem As DataRow In schemaReader.Rows
                If rowItem.Item("TABLE_NAME") = TableName Then
                    returnVal = True
                    Return returnVal
                End If
            Next
        Finally
            objCommand.Parameters.Clear()
            If objConnection.State <> Data.ConnectionState.Closed Then
                objConnection.Close()
            End If
        End Try
    End Function

    Public Function ExecuteNonQuery(ByVal query As String, ByVal commandtype As CommandType, ByVal connectionstate As ConnectionState) As Integer
        Dim queryText As String = Nothing
        Select Case _CurrentDatabaseType
            Case DatabaseType.MySQL
                queryText = GetNamedParameters(query, objCommand.Parameters)
            Case DatabaseType.SQLServer2005
                queryText = GetNamedParameters(query, objCommand.Parameters)
            Case DatabaseType.SQLServer2000
                queryText = GetNamedParameters(query, objCommand.Parameters)
            Case DatabaseType.Oracle
                queryText = GetNamedParameters(query, objCommand.Parameters)
            Case Else
                queryText = query
        End Select
        objCommand.CommandText = queryText
        objCommand.CommandType = commandtype
        Dim i As Integer = -1
        Try
            If objConnection.State = System.Data.ConnectionState.Closed Then
                objConnection.Open()
            End If
            i = objCommand.ExecuteNonQuery
        Finally
            objCommand.Parameters.Clear()
            If connectionstate = connectionstate.CloseOnExit Then
                objConnection.Close()
            End If
        End Try
        Return i
    End Function

    Public Function ExecuteScalar(ByVal query As String) As Object
        Return ExecuteScalar(query, CommandType.Text, ConnectionState.CloseOnExit)
    End Function

    Public Function ExecuteScalar(ByVal query As String, ByVal commandtype As CommandType) As Object
        Return ExecuteScalar(query, commandtype, ConnectionState.CloseOnExit)
    End Function

    Public Function ExecuteScalar(ByVal query As String, ByVal connectionstate As ConnectionState) As Object
        Return ExecuteScalar(query, CommandType.Text, connectionstate)
    End Function

    Public Function ExecuteScalar(ByVal query As String, ByVal commandtype As CommandType, ByVal connectionstate As ConnectionState) As Object
        Dim queryText As String = Nothing
        Select Case _CurrentDatabaseType
            Case DatabaseType.MySQL
                queryText = GetNamedParameters(query, objCommand.Parameters)
            Case DatabaseType.SQLServer2005
                queryText = GetNamedParameters(query, objCommand.Parameters)
            Case DatabaseType.SQLServer2000
                queryText = GetNamedParameters(query, objCommand.Parameters)
            Case DatabaseType.Oracle
                queryText = GetNamedParameters(query, objCommand.Parameters)
            Case Else
                queryText = query
        End Select
        objCommand.CommandText = queryText
        objCommand.CommandType = commandtype
        Dim o As Object = Nothing
        Try
            If objConnection.State = System.Data.ConnectionState.Closed Then
                objConnection.Open()
            End If
            o = objCommand.ExecuteScalar
        Finally
            objCommand.Parameters.Clear()
            If connectionstate = connectionstate.CloseOnExit Then
                objConnection.Close()
            End If
        End Try
        Return o
    End Function

    Private Function GetNamedParameters(ByVal query As String, ByVal Parameters As DbParameterCollection) As String
        Dim returnValue As String = Nothing
        query.Split("?")
        Dim i As Integer = 0
        For Each Character As Char In query.ToCharArray
            If Character = "?" Then
                Select Case _CurrentDatabaseType
                    Case DatabaseType.SQLServer2005
                        returnValue = returnValue & Parameters(i).ParameterName
                    Case DatabaseType.SQLServer2000
                        returnValue = returnValue & Parameters(i).ParameterName
                    Case DatabaseType.Oracle
                        returnValue = returnValue & Parameters(i).ParameterName.Replace("@", ":")
                    Case DatabaseType.MySQL
                        returnValue = returnValue & Parameters(i).ParameterName.Replace("@", "?")
                End Select
                i = i + 1
            Else
                returnValue = returnValue & Character
            End If
        Next
        Return returnValue
    End Function

    Public Function ExecuteReader(ByVal query As String) As Generic.List(Of DbDataRecord)
        Return ExecuteReader(query, CommandType.Text, ConnectionState.CloseOnExit)
    End Function

    Public Function ExecuteReader(ByVal query As String, ByVal commandtype As CommandType) As Generic.List(Of DbDataRecord)
        Return ExecuteReader(query, commandtype, ConnectionState.CloseOnExit)
    End Function

    Public Function ExecuteReader(ByVal query As String, ByVal connectionstate As ConnectionState) As Generic.List(Of DbDataRecord)
        Return ExecuteReader(query, CommandType.Text, connectionstate)
    End Function

    Public Function ExecuteReader(ByVal query As String, ByVal commandtype As CommandType, ByVal connectionstate As ConnectionState) As Generic.List(Of DbDataRecord)
        Dim queryText As String = Nothing
        Dim returnReader As New Generic.List(Of DbDataRecord)
        Select Case _CurrentDatabaseType
            Case DatabaseType.MySQL
                queryText = GetNamedParameters(query, objCommand.Parameters)
            Case DatabaseType.SQLServer2005
                queryText = GetNamedParameters(query, objCommand.Parameters)
            Case DatabaseType.SQLServer2000
                queryText = GetNamedParameters(query, objCommand.Parameters)
            Case DatabaseType.Oracle
                queryText = GetNamedParameters(query, objCommand.Parameters)
            Case Else
                queryText = query
        End Select
        objCommand.CommandText = queryText
        objCommand.CommandType = commandtype
        Dim reader As DbDataReader = Nothing
        Try
            If objConnection.State = System.Data.ConnectionState.Closed Then
                objConnection.Open()
            End If
            If connectionstate = connectionstate.CloseOnExit Then
                reader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
            Else
                reader = objCommand.ExecuteReader
            End If
            For Each record As DbDataRecord In reader
                returnReader.Add(record)
            Next
        Finally
            objCommand.Parameters.Clear()
        End Try
        Return returnReader
    End Function

    Public Function ExecuteDataSet(ByVal query As String) As DataSet
        Return ExecuteDataSet(query, CommandType.Text, ConnectionState.CloseOnExit)
    End Function

    Public Function ExecuteDataSet(ByVal query As String, ByVal commandtype As CommandType) As DataSet
        Return ExecuteDataSet(query, commandtype, ConnectionState.CloseOnExit)
    End Function

    Public Function ExecuteDataSet(ByVal query As String, ByVal connectionstate As ConnectionState) As DataSet
        Return ExecuteDataSet(query, CommandType.Text, connectionstate)
    End Function

    Public Function ExecuteDataSet(ByVal query As String, ByVal commandtype As CommandType, ByVal connectionstate As ConnectionState) As DataSet
        Dim queryText As String = Nothing
        Select Case _CurrentDatabaseType
            Case DatabaseType.MySQL
                queryText = GetNamedParameters(query, objCommand.Parameters)
            Case DatabaseType.SQLServer2005
                queryText = GetNamedParameters(query, objCommand.Parameters)
            Case DatabaseType.SQLServer2000
                queryText = GetNamedParameters(query, objCommand.Parameters)
            Case DatabaseType.Oracle
                queryText = GetNamedParameters(query, objCommand.Parameters)
            Case Else
                queryText = query
        End Select
        Dim adapter As DbDataAdapter = objFactory.CreateDataAdapter
        objCommand.CommandText = queryText
        objCommand.CommandType = commandtype
        adapter.SelectCommand = objCommand
        Dim ds As DataSet = New DataSet
        Try
            adapter.Fill(ds)
        Finally
            objCommand.Parameters.Clear()
            If connectionstate = connectionstate.CloseOnExit Then
                If objConnection.State = System.Data.ConnectionState.Open Then
                    objConnection.Close()
                End If
            End If
        End Try
        Return ds
    End Function

    Public Sub Dispose() Implements System.IDisposable.Dispose
        objConnection.Close()
        objConnection.Dispose()
        objCommand.Parameters.Clear()
        objCommand.Dispose()
    End Sub
End Class

Public Enum ConnectionState
    KeepOpen
    CloseOnExit
End Enum
